<?php

include 'DBconn.php';

class EnquiryDetails {
public function enquiryListPopupHeader() {
	$query="select enq_serialnumber,enq_code,enq_date,enq_remarks from enquiry_header where activestatus='1'";
	$result = mysql_query($query) or die(mysql_error());
    $ret = array();
     while ($row = mysql_fetch_object($result)) 
		{
			
				$enq_serialnumber_fk= (int) $row->enq_serialnumber;
			
				$resSup = mysql_query("SELECT DISTINCT supplier_fk FROM enquiry_detail where enq_serialnumber_fk='$enq_serialnumber_fk'") or die(mysql_error());
				while ($rowsup = mysql_fetch_object($resSup)) 
				{
			$temp = new stdClass();
            $temp->enq_code = $row->enq_code;
			$temp->enq_date = date("d-m-Y", strtotime($row->enq_date));
            $temp->enq_remarks = $row->enq_remarks;
			
			$sup_sno_fk=$rowsup->supplier_fk;
			$temp->supplier_fk=$rowsup->supplier_fk;
				$resSup1=mysql_query("select client_name from client_header where serialnumber='$sup_sno_fk' and activestatus='1' ");
				$res2=mysql_fetch_array($resSup1);
				$temp->client_name=$res2['client_name'];
				$ret[] = $temp;
				}
			
		}
	return $ret;
}
    public function enquiryListPopup() {
        $query = "Select * from enquiry_detail as ed LEFT JOIN product_header as ph ON ph.serialnumber=ed.pro_serialnumber_fk and ph.activestatus=1 
		LEFT JOIN enquiry_header as eh ON eh.activestatus=1 and eh.enq_serialnumber=ed.enq_serialnumber_fk Where ed.activestatus=1 ORDER BY ed.enq_serialnumber_fk";
        $result = mysql_query($query) or die(mysql_error());
        $ret = array();
		
        while ($row = mysql_fetch_object($result)) 
		{
            
			/*$enq_serialnumber_fk=$row->enq_serialnumber_fk;
			$resSup = mysql_query("SELECT DISTINCT supplier_fk FROM enquiry_detail where enq_serialnumber_fk='$enq_serialnumber_fk'") or die(mysql_error());
			while ($rowSup = mysql_fetch_object($resSup)) 
			{*/
			$temp = new VoEnquiryPopupList;
			$temp->enq_detserialnumber = (int) $row->enq_detserialnumber;
            $temp->pro_serialnumber_fk = (int) $row->pro_serialnumber_fk;
			$temp->pro_name = $row->pro_name;
            $temp->part_number = $row->part_number;
			$temp->stamp_number = $row->stamp_number;
			$temp->pro_description = $row->pro_description;
			$temp->cre_datetime = $row->cre_datetime;
			
			
			$unitPrice = (double)$row->unit_price;
			$current_rate = (double)$row->current_rate;
			$temp->price_per_unit = round(($unitPrice * $current_rate),4);
			$temp->netprice = (int) $row->netprice;
            $temp->pro_qty = (int) $row->pro_qty;
            $temp->pro_remarks = $row->pro_remarks;
            $temp->enq_serialnumber = $row->enq_serialnumber;
            $temp->enq_code = $row->enq_code;
			$temp->supplier_currency_fk = (int)$row->supplier_currency_fk;
			$currecyFk=(int)$row->supplier_currency_fk;
			$res = mysql_query("select currency_name from currency_header where serialnumber='$currecyFk'") or die(mysql_error());
			$currency=mysql_fetch_array($res);
			$temp->currency_name = $currency['currency_name'];
            
			$temp->isSelected = false;
			
			$sup_sno_fk=(int)$row->supplier_fk;
			$temp->supplier_fk=$sup_sno_fk;
			$resSup1=mysql_query("select client_name from client_header where serialnumber='$sup_sno_fk' and activestatus='1' ");
			$res2=mysql_fetch_array($resSup1);
			$temp->client_name=$res2['client_name'];
			$ret[] = $temp;
			//}
	       
        }

        return $ret;
    }

    public function getMailDetails() {
        $temp = new VoEnquiryEmail();
        $temp->baseRemarksEmail = "Sample Remarks";
        $temp->enq_noEmail = "Sample ENQ.NO";
        $temp->pro_nameEmail[] = '';
        $temp->stamp_noEmail[] = '';
        $temp->prat_noEmail[] = '';
        $temp->model_noEmail[] = '';
        $temp->quantityEmail[] = '';
        $temp->remarksEmail[] = '';
        $temp->supplierEmail[] = '';
        return $temp;
    }

    public function SendMail($VObject) {
        $from = "prabhu@360i.in";
        $subject = "Product Enquiry";
        $headers = 'MIME-Version: 1.0' . "\r\n";
        $headers .= 'Content-type: text/html; charset=iso-8859-1' . "\r\n";
        $headers .= "From: $from";
        $headers .= "MIME-Version: 1.0\r\n";
        $headers .= "Content-Type: text/html; charset=ISO-8859-1\r\n";
        $headers .="Content-Type: text/css;";
        $message = '<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">';
        $message.='<html xmlns="http://www.w3.org/1999/xhtml" xml:lang="en" lang="en">';
        $message.='<head><meta http-equiv="content-type" content="text/html; charset=iso-8859-1" />';
        $message.='<meta name="author" content="" />';
        $message.='<title>Equiry</title>';
        $message.='<style type="text/css">table, td, th{border:1px solid red;text-align:center;}';
        $message.='th{background-color:blue;color:white;}</style>';
        $message.='</head>';
        $message.='<body>';
        $message.='<table>';
        $message.='<th>Product Name</th><th>Part No</th><th>stamping no</th><th>Other Replacement Parts</th><th>Quantity</th><th>Remarks</th>';
        foreach ($VObject->pro_nameEmail as $r => $value) {
            $part_no = mysql_real_escape_string($VObject->prat_noEmail[$r]);
            $stamp_no = mysql_real_escape_string($VObject->stamp_noEmail[$r]);
            $model_no = mysql_real_escape_string($VObject->model_noEmail[$r]);
            $pro_qty = mysql_real_escape_string($VObject->quantityEmail[$r]);
            $pro_remarks = mysql_real_escape_string($VObject->remarksEmail[$r]);
            $message.='<tr><td>';
            $message.="$value";
            $message.='</td><td>';
            $message.="$part_no";
            $message.='</td><td>';
            $message.="$stamp_no";
            $message.='</td><td>';
            $message.="$model_no";
            $message.='</td><td>';
            $message.="$pro_qty";
            $message.='</td><td>';
            $message.="$pro_remarks";
            $message.='</td><td>';
            $message.='</td><td></tr>';
        }
        $message.='</table>';
        $message.='Enquiry Remarks:';
        $message.=$VObject->baseRemarksEmail;
        $message.='</body></html>';
        foreach ($VObject->supplierEmail as $r => $to) {
            @mail($to, $subject, $message, $headers);
        }
        return "true";
    }

    public function EnquiryAutoCode() {
        $query = "select enq_code from enquiry_header order by enq_serialnumber  desc limit 1 ";
        $result = mysql_query($query) or die(mysql_error());
        $row = mysql_fetch_array($result);
        if ($row[0] != "") {
            $str = substr($row[0], 0, 3);
            $str2 = substr($row[0], 3, 4);
            ++$str2 . "<br>";
            $str3 = $str . $str2;
        } else {
            $str3 = "ENQ1001";
        }
        return $str3;
    }

    public function getEnquiryByCode($enq_serialnumber) {
        $query = "select * from enquiry_header where enq_serialnumber='$enq_serialnumber' and activestatus=1";
         $s = mysql_query($query) or die(mysql_error());
		 $row = mysql_fetch_object($s);
		 
			$temp = new VoEnquiryHeader();
            $temp->enq_serialnumber = (int) $row->enq_serialnumber;
            $temp->enq_code = $row->enq_code;
			$temp->total_amount = (double) $row->total_amount;
            $temp->total_qty = (double) $row->total_qty;
			//$temp->currency_serialnumber_fk = (int) $row->currency_serialnumber_fk;
			$temp->current_rate = (double) $row->current_rate;
			$temp->enq_date = date("d-m-Y", strtotime($row->enq_date));
            $temp->enq_remarks = $row->enq_remarks;
            $temp->cre_userid_fk = (int) $row->cre_userid_fk;
            $temp->cre_datetime = $row->cre_datetime;
            $temp->activestatus = (boolean) $row->activestatus;
			//$temp->enq_edit = (int) $row->enq_edit;
			
			//$temp->supplier_fk[] = (int)$row2->supplier_fk;
			
			
           $query1 = "select DISTINCT supplier_fk from enquiry_detail where enq_serialnumber_fk='$row->enq_serialnumber' and activestatus=1";
            $s1 = mysql_query($query1);
            while ($row1 = mysql_fetch_object($s1)) {
                $temp->supplier_fk[] = (int) $row1->supplier_fk;
            }
			
            $query2 = "select DISTINCT ed.pro_serialnumber_fk,ed.pro_total_price,ed.pro_qty,ed.pro_remarks,ph.pro_name,ph.part_number,ph.stamp_number,
			ph.pro_description,ph.model_no,ph.netprice from enquiry_detail as ed LEFT JOIN product_header as ph ON ph.serialnumber=ed.pro_serialnumber_fk and ph.activestatus=1 
			where ed.enq_serialnumber_fk='$row->enq_serialnumber' and ed.activestatus=1";
            $s2 = mysql_query($query2);
            while ($row2 = mysql_fetch_object($s2)) {
              //  $temp->enq_detserialnumber[] = (int) $row2->enq_detserialnumber;
                $temp->pro_serialnumber_fk[] = (int) $row2->pro_serialnumber_fk;
                $temp->pro_qty[] = (int) $row2->pro_qty;
					
					//onhand
			//$resultH = mysql_query("select Sum(on_hand) as 'on_hand',Sum(on_sale) as 'on_sale',Sum(in_order) as 'in_order' from stock_header where pro_serialnumber='$row2->pro_serialnumber_fk' and activestatus=1");
				$resultH = mysql_query("SELECT on_hand from stock_header where activestatus=1 and pro_serialnumber='$row2->pro_serialnumber_fk'") or die(mysql_error());
					$on_hand_stock=mysql_fetch_array($resultH);
					$temp->on_hand[]= (int)$on_hand_stock['on_hand'];
			
			//list price			
			$temp->netprice[]=(double)$row2->netprice;
			
					$uquery="select unit_price,supplier_currency_fk from enquiry_detail where pro_serialnumber_fk='$row2->pro_serialnumber_fk' and enq_serialnumber_fk='$row->enq_serialnumber' and activestatus=1";
					$us = mysql_query($uquery);
					$unitPrice=array();
					$supplierCurrency=array();
					$supplierCurrencyName=array();
					 while ($rowu = mysql_fetch_object($us)) {
						$unitPrice[]=(double) $rowu->unit_price;
						$supplierCurrency[]=(int) $rowu->supplier_currency_fk;	
						$squery="select currency_name from currency_header where serialnumber='$rowu->supplier_currency_fk' and activestatus=1";
					 $ss = mysql_query($squery);
					 $rows = mysql_fetch_array($ss);
					$supplierCurrencyName[]=$rows['currency_name'];						
					 }
					 
				$temp->sup_currency[] = $supplierCurrencyName;
				$temp->unit_price[] = $unitPrice;
				$temp->supplier_currency_fk[] =$supplierCurrency;	
                $temp->pro_total_price[] = (double) $row2->pro_total_price;
                $temp->pro_remarks[] = $row2->pro_remarks;
				$temp->pro_name[] = $row2->pro_name;
				$temp->part_number[] = $row2->part_number;
				$temp->stamp_number[] = $row2->stamp_number;
				$temp->pro_description[] = $row2->pro_description;
				$temp->model_no[] = $row2->model_no;
				
				
            }
        return $temp;
    }

    public function getAllEnquires() {
        $ret = array();
        $query = "Select *,IF((Select count(*) from enq_det_po where activestatus=1 and enq_detserialnumber_fk IN(Select enq_detserialnumber from enquiry_detail as ed where ed.activestatus=1 and ed.enq_serialnumber_fk=eh.enq_serialnumber)) > 0,1,0) as enq_edit from enquiry_header as eh where eh.activestatus=1";
        $s = mysql_query($query) or die(mysql_error());
        while ($row = mysql_fetch_object($s)) {
            $temp = new VoEnquiryHeader();
            $temp->enq_serialnumber = (int) $row->enq_serialnumber;
            $temp->enq_code = $row->enq_code;
			$temp->total_amount = (double) $row->total_amount;
            $temp->total_qty = (double) $row->total_qty;
			//$temp->currency_serialnumber_fk = (int) $row->currency_serialnumber_fk;
			$temp->current_rate = (double) $row->current_rate;
			$temp->enq_date = date("d-m-Y", strtotime($row->enq_date));
            $temp->enq_remarks = $row->enq_remarks;
            $temp->cre_userid_fk = (int) $row->cre_userid_fk;
            $temp->cre_datetime = $row->cre_datetime;
            $temp->activestatus = (boolean) $row->activestatus;
			$temp->enq_edit = (int) $row->enq_edit;
			
			//$temp->supplier_fk[] = (int)$row2->supplier_fk;
			
			
           $query1 = "select DISTINCT supplier_fk from enquiry_detail where enq_serialnumber_fk='$row->enq_serialnumber' and activestatus=1";
            $s1 = mysql_query($query1);
            while ($row1 = mysql_fetch_object($s1)) {
                $temp->supplier_fk[] = (int) $row1->supplier_fk;
            }
			
            $query2 = "select DISTINCT ed.pro_serialnumber_fk,ed.pro_total_price,ed.pro_qty,ed.pro_remarks,ph.pro_name,ph.part_number,ph.stamp_number,
			ph.pro_description,ph.model_no,ph.netprice from enquiry_detail as ed LEFT JOIN product_header as ph ON ph.serialnumber=ed.pro_serialnumber_fk and ph.activestatus=1 
			where ed.enq_serialnumber_fk='$row->enq_serialnumber' and ed.activestatus=1";
            $s2 = mysql_query($query2);
            while ($row2 = mysql_fetch_object($s2)) {
              //  $temp->enq_detserialnumber[] = (int) $row2->enq_detserialnumber;
                $temp->pro_serialnumber_fk[] = (int) $row2->pro_serialnumber_fk;
                $temp->pro_qty[] = (int) $row2->pro_qty;
					
					//onhand
					$resultH = mysql_query("SELECT on_hand from stock_header where activestatus=1 and pro_serialnumber='$row2->pro_serialnumber_fk'") or die(mysql_error());
					$on_hand_stock=mysql_fetch_array($resultH);
					$temp->on_hand[]= (int)$on_hand_stock['on_hand'];
			
			//list price			
			$temp->netprice[]=(double)$row2->netprice;
			
					$uquery="select unit_price,supplier_currency_fk,supplier_fk from enquiry_detail where pro_serialnumber_fk='$row2->pro_serialnumber_fk' and enq_serialnumber_fk='$row->enq_serialnumber' and activestatus=1";
					$us = mysql_query($uquery);
					$unitPrice=array();
					$supplierCurrency=array();
					$supplierCurrencyName=array();
					
					 while ($rowu = mysql_fetch_object($us)) {
						$supFk='s'.$rowu->supplier_fk;
						$unitPrice[$supFk]=(double) $rowu->unit_price;
						$supplierCurrency[$supFk]=(int) $rowu->supplier_currency_fk;	
						$squery="select currency_name from currency_header where serialnumber='$rowu->supplier_currency_fk' and activestatus=1";
					 $ss = mysql_query($squery);
					 $rows = mysql_fetch_array($ss);
					$supplierCurrencyName[$supFk]=$rows['currency_name'];						
					 }
					 
				$temp->sup_currency[] = $supplierCurrencyName;
				$temp->unit_price[] = $unitPrice;
				$temp->supplier_currency_fk[] =$supplierCurrency;	
                $temp->pro_total_price[] = (double) $row2->pro_total_price;
                $temp->pro_remarks[] = $row2->pro_remarks;
				$temp->pro_name[] = $row2->pro_name;
				$temp->part_number[] = $row2->part_number;
				$temp->stamp_number[] = $row2->stamp_number;
				$temp->pro_description[] = $row2->pro_description;
				$temp->model_no[] = $row2->model_no;
				
				
            }
            $ret[] = $temp;
          //  mysql_free_result($s1);
            mysql_free_result($s2);
        }
        mysql_free_result($s);
        return $ret;
    }

	public function addNewEnquiryHeader($VObject) {
        $s = new EnquiryDetails();
        $enqCode = $s->EnquiryAutoCode();
        $query = "insert into enquiry_header(enq_code,total_amount,total_qty,current_rate,enq_date,enq_remarks,cre_userid_fk,cre_datetime,activestatus) values ('$enqCode','1','$VObject->total_qty','$VObject->current_rate','$VObject->enq_date','$VObject->enq_remarks','$VObject->cre_userid_fk','$VObject->cre_datetime',1)";
        $s = mysql_query($query) or die(mysql_error());
       if ($s) {
            $last_id = mysql_insert_id();
            foreach ($VObject->supplier_serialnumber_fk as $r => $value) {
			//$currentRate=mysql_real_escape_string($VObject->current_rate[$r]);
			//$SupcurrencySerialFk=mysql_real_escape_string($VObject->currency_serialnumber_fk[$r]);
			//$unit_price=mysql_real_escape_string($VObject->unit_price[$r]);
				$supplier_fk=$value;
				 foreach ($VObject->pro_serialnumber_fk as $rp => $provalue) {
                $pro_qty = mysql_real_escape_string($VObject->pro_qty[$rp]);
				
				$unit_price = mysql_real_escape_string($VObject->unit_price[$rp]['s'.$value]);
				//$unit_price=3;
				//$supplier_currency_fk=1;
				$supplier_currency_fk = mysql_real_escape_string($VObject->supplier_currency_fk[$rp]['s'.$value]);
               // $pro_total_price = mysql_real_escape_string($VObject->pro_total_price[$rp]);
				$pro_total_price = 11;
                $pro_remarks = mysql_real_escape_string($VObject->pro_remarks[$rp]);
                $query2 = "insert into enquiry_detail(enq_serialnumber_fk,pro_serialnumber_fk,pro_qty,unit_price,pro_total_price,pro_remarks,supplier_fk,supplier_currency_fk,cre_userid_fk,cre_datetime,activestatus) values('$last_id','$provalue','$pro_qty','$unit_price','$pro_total_price','$pro_remarks','$supplier_fk','$supplier_currency_fk','$VObject->cre_userid_fk','$VObject->cre_datetime',1)";
                mysql_query($query2) or die(mysql_error());
            }
            }
           
        }

		return $enqCode;
       // return $this->getEnquiryByCode($last_id);
    }
	
    public function editEnquiry($VObject) {
        $query = "update enquiry_header set enq_code='$VObject->enq_code',total_amount='11',total_qty='$VObject->total_qty',current_rate='$VObject->current_rate',enq_date='$VObject->enq_date',enq_remarks='$VObject->enq_remarks' where enq_serialnumber='$VObject->enq_serialnumber'";
        $s = mysql_query($query) or die(mysql_error());
		 mysql_query("update enquiry_detail set activestatus=0 where enq_serialnumber_fk='$VObject->enq_serialnumber'") or die(mysql_error());
      if ($s) {
           
            foreach ($VObject->supplier_serialnumber_fk as $r => $value) {
			//$currentRate=mysql_real_escape_string($VObject->current_rate[$r]);
			//$SupcurrencySerialFk=mysql_real_escape_string($VObject->currency_serialnumber_fk[$r]);
			//$unit_price=mysql_real_escape_string($VObject->unit_price[$r]);
				$supplier_fk=$value;
				 foreach ($VObject->pro_serialnumber_fk as $rp => $provalue) {
                $pro_qty = mysql_real_escape_string($VObject->pro_qty[$rp]);
				$unit_price = mysql_real_escape_string($VObject->unit_price[$rp]['s'.$value]);
				$supplier_currency_fk = mysql_real_escape_string($VObject->supplier_currency_fk[$rp]['s'.$value]);
               // $pro_total_price = mysql_real_escape_string($VObject->pro_total_price[$rp]);
				$pro_total_price = 11;
                $pro_remarks = mysql_real_escape_string($VObject->pro_remarks[$rp]);
                $query2 = "insert into enquiry_detail(enq_serialnumber_fk,pro_serialnumber_fk,pro_qty,unit_price,pro_total_price,pro_remarks,supplier_fk,supplier_currency_fk,cre_userid_fk,cre_datetime,activestatus) values('$VObject->enq_serialnumber','$provalue','$pro_qty','$unit_price','$pro_total_price','$pro_remarks','$supplier_fk','$supplier_currency_fk','$VObject->cre_userid_fk','$VObject->cre_datetime',1)";
                mysql_query($query2) or die(mysql_error());
            }
            }
           
        }
    }

    public function deleteEnquiry($serialnumber) {      
   
        $query2 = ("update enquiry_detail set activestatus=0 where enq_serialnumber_fk='$serialnumber'") or die(mysql_error());
        mysql_query($query2) or die(mysql_error());
        $query3 = ("update enquiry_header set activestatus=0 where enq_serialnumber='$serialnumber'") or die(mysql_error());
        mysql_query($query3) or die(mysql_error());
        return $serialnumber;
    }

    public function enqSupplierData() {
        $ret = array();
        $query = "select * from enquiry_header where activestatus=1";
        $s = mysql_query($query) or die(mysql_error());
        while ($row = mysql_fetch_object($s)) {
            $temp = new voEnquirySupplier();
            $temp->enq_serialnumber = (int) $row->enq_serialnumber;
            $query1 = "SELECT DISTINCT supplier_fk FROM enquiry_detail where activestatus=1 and enq_serialnumber_fk='$row->enq_serialnumber'";
            $s1 = mysql_query($query1);
            while ($row1 = mysql_fetch_object($s1)) {
                $temp->supplier_fk[] = (int) $row1->supplier_fk;
            }
            $ret[] = $temp;
        }
        return $ret;
    }

    public function enqHeaderData() {
        $ret = array();
        $query1 = "select * from enquiry_header where activestatus=1";
        $s1 = mysql_query($query1);
        while ($row1 = mysql_fetch_object($s1)) {
            $temp = new voEnqHeaderDetails();
            $temp->enq_serialnumber = (int) $row1->enq_serialnumber;
            $temp->enq_code = $row1->enq_code;
			$temp->total_qty = $row1->total_qty;
			$temp->enq_remarks = $row1->enq_remarks;
			$temp->isSelected = false;
		
			
            $ret[] = $temp;
        }

        return $ret;
    }

}

class voEnquirySupplier {

    var $enq_serialnumber;
    var $supplier_fk;

}

class voEnqHeaderDetails {

    var $enq_serialnumber;
    var $enq_code;
	var $total_qty;
    var $enq_remarks;
    var $isSelected;

}

class voEnquiryEmail {

    //Values For Email
    var $enq_noEmail;
    var $supplierEmail;
    var $pro_nameEmail;
    var $prat_noEmail;
    var $model_noEmail;
    var $stamp_noEmail;
    var $quantityEmail;
    var $remarksEmail;
    var $baseRemarksEmail;

}

class VoEnquiryPopupList {
    var $enq_serialnumber;
    var $enq_code;
	var $supplier_currency_fk;
	var $netprice;
	var $enq_detserialnumber;
    var $pro_serialnumber_fk;
    var $pro_qty;
    var $pro_remarks;
    var $pro_name;
	var $stamp_number;
    var $part_number;
	var $pro_description;
    var $price_per_unit;
	var $currency_name;
	var $supplier_fk;
    var $isSelected;
	var $cre_datetime;
	var $client_name;
	
}

class VoEnquiryHeader {
    var $enq_serialnumber;
    var $enq_code;
    var $enq_date;
    var $total_amount;
	var $total_qty;
    var $enq_remarks;
	var $enq_edit;
	var $currency_serialnumber_fk;
    var $current_rate;
   // var $enq_sup_serialnumber;
    var $supplier_fk;
    var $enq_detserialnumber;
    var $pro_serialnumber_fk;
	var $pro_name;
	var $part_number;
	var $stamp_number;
	var $pro_description;
	var $model_no;
    var $pro_qty;
	var $unit_price;
	var $supplier_currency_fk;
	var $sup_currency;
    var $pro_total_price;
    var $pro_remarks;
    var $cre_userid_fk;
	var $netprice;
	var $on_hand;
    var $cre_datetime;
    var $activestatus;
}

//$s = new EnquiryDetails();
//$s->addNewEnquiryHeader();
?>